package com.sunni.repository.impl;

import com.sunni.entity.Book;
import com.sunni.entity.Bookcase;
import com.sunni.repository.BookRepository;
import com.sunni.utils.JDBCTool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookRepositoryImpl implements BookRepository {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    @Override
    public List<Book> findAll(Integer index , Integer LIMIT) {
        List<Book> bookList = new ArrayList<>();
        try {
            connection = JDBCTool.getConnection();
            String sql =  "select * from book,bookcase where book.bookcaseid=bookcase.id limit ?,?;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,index);
            preparedStatement.setInt(2,LIMIT);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Integer id = resultSet.getInt("id");
                String name  = resultSet.getString("name");
                String author= resultSet.getString("author");
                String publish = resultSet.getString("publish");
                Integer pages = resultSet.getInt("pages");
                Double price = resultSet.getDouble("price");
                Bookcase bookcase = new Bookcase(resultSet.getInt("bookcaseid"),resultSet.getString(10));
                Integer abled = resultSet.getInt("abled");
                bookList.add(new Book(id,name,author,publish,pages,price,bookcase,abled));
            }
        } catch (SQLException e) {
            System.out.println(e);
        } finally {
            JDBCTool.close(connection,preparedStatement,resultSet);
        }
        return bookList;
    }

    @Override
    public Integer getProjectTotal() {
        Integer projectTotal=0;
        try {
            connection = JDBCTool.getConnection();
            String sql = "select count(*) from book,bookcase where book.bookcaseid=bookcase.id;";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                projectTotal=resultSet.getInt(1);
            }
        } catch (SQLException e) {
            System.out.println(e);
        }finally {
            JDBCTool.close(connection,preparedStatement,resultSet);
        }
        return projectTotal;
    }
}
